import pymysql
from dbutils.pooled_db import PooledDB

# 创建数据库连接池
pool = PooledDB(
    creator=pymysql,
    maxconnections=6,
    mincached=2,
    maxcached=5,
    blocking=True,
    host="localhost",
    port=3306,
    user="root",
    password="123456",
    database="emoseek",
    charset="utf8mb4"
)

# 获取数据库连接
def get_conn():
    return pool.connection(), pool.connection().cursor()


# 释放资源
def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()


# 查询user表中的所有
def find_all():
    # 获取数据库连接
    conn, cursor = get_conn()
    # 执行查询操作
    sql = "select * from user"
    count = cursor.execute(sql)
    print(f"查询到了{count}条数据")
    # 获取数据的详细信息
    result = cursor.fetchall()
    for item in result:
        print(f"username:{item[1]}, password:{item[2]}")
    print(result)
    # 释放资源
    close_conn(conn, cursor)
    # 返回结果
    return result
    # cursor.close()
    # conn.close()


# 添加数据
def insert_into(username, password):
    """插入新用户（修正版）"""
    conn, cursor = get_conn()
    try:
        sql = "INSERT INTO user (username, password) VALUES (%s, %s)"
        count = cursor.execute(sql, (username, password))
        conn.commit()
        return count > 0
    except pymysql.Error as e:
        print(f"数据库错误: {e}")
        conn.rollback()
        return False
    finally:
        close_conn(conn, cursor)


# 删除
def delete_info():
    id = input("请输入要删除的id：")
    conn, cursor = get_conn()
    # 执行删除操作
    sql = "delete from user where id = %s"
    count = cursor.execute(sql, id)
    if count > 0:
        print("删除成功")
    else:
        print("删除失败")
    conn.commit()
    close_conn(conn, cursor)


# 更新
def update_info():
    id = input("请输入要修改的id：")
    username = input("请输入新的username：")
    password = input("请输入新的password：")
    conn, cursor = get_conn()
    # 执行更新操作
    sql = "update user set username=%s, password=%s where id = %s"
    # 按照顺序绑定参数
    count = cursor.execute(sql, (username, password, id))
    if count > 0:
        print("更新成功")
    else:
        print("更新失败")
    conn.commit()
    close_conn(conn, cursor)


def insert_user_with_phone(username, password, phone):
    """插入新用户（含手机号）"""
    conn, cursor = get_conn()
    try:
        sql = "INSERT INTO user (username, password, phone) VALUES (%s, %s, %s)"
        count = cursor.execute(sql, (username, password, phone))
        conn.commit()
        return count > 0
    except pymysql.Error as e:
        print(f"数据库错误: {e}")
        conn.rollback()
        return False
    finally:
        close_conn(conn, cursor)


def update_password_by_id(user_id, new_password):
    conn, cursor = get_conn()
    try:
        sql = "UPDATE user SET password=%s WHERE id=%s"
        count = cursor.execute(sql, (new_password, user_id))
        conn.commit()
        return count > 0
    except pymysql.Error as e:
        print(f"数据库错误: {e}")
        conn.rollback()
        return False
    finally:
        close_conn(conn, cursor)


if __name__ == '__main__':
    # get_conn()
    find_all()
    # insert_into()
    delete_info()
    # update_info()
    find_all()
